import pymysql as mysql
import pandas as pd
import datetime
today = datetime.datetime.now().strftime('%Y-%m-%d')
host = "localhost"
user = "s"
passwd = "s"
#passwd = "sdh478174277"
#passwd = "songdonghui"
db = "brfdb"
conn = mysql.connect(host=host, user=user, password=passwd, database=db)
#path = "D:/DeskTop/brf-db/model/blood.xlsx"
path = "~/brfDB/brf-db/model/blood.xlsx"
df_base = pd.read_excel(path)
df = pd.DataFrame(df_base)
#writer = pd.ExcelWriter(path)
# df = df_base.astype(object).where(pd.notnull(df_base), None)
sql = "SELECT * FROM blood"
cursor = conn.cursor()
# cursor.execute("./tools.sql")
xlsxdb = []
try:
    cursor.execute(sql)
    data = cursor.fetchall()
    for i in data:
        if i:
            i = list(i)
        for x in i:
            # print(x,i.index(x))
            xlsxdb.append(x)
except:
    conn.rollback()
def push():
    df.loc[1][2] = xlsxdb[1]
    df.loc[1][6] = xlsxdb[2]
    df.loc[1][12] = xlsxdb[3]
    df.loc[1][16] = xlsxdb[4]
    df.loc[2][2] = xlsxdb[5]
    df.loc[2][6] = xlsxdb[6]
    df.loc[2][12] = xlsxdb[7]
    df.loc[3][2] = xlsxdb[8]
    df.loc[5][4] = xlsxdb[9]
    df.loc[6][4] = xlsxdb[10]
    df.loc[7][4] = xlsxdb[11]
    df.loc[8][4] = xlsxdb[12]
    df.loc[9][4] = xlsxdb[13]
    df.loc[10][4] = xlsxdb[14]
    df.loc[11][4] = xlsxdb[15]
    df.loc[12][4] = xlsxdb[16]
    df.loc[13][4] = xlsxdb[17]
    df.loc[14][4] = xlsxdb[18]
    df.loc[5][16] = xlsxdb[19]
    df.loc[6][16] = xlsxdb[20]
    df.loc[7][16] = xlsxdb[21]
    df.loc[8][16] = xlsxdb[22]
    df.loc[9][16] = xlsxdb[23]
    df.loc[10][16] = xlsxdb[24]
    df.loc[11][16] = xlsxdb[25]
    df.loc[12][16] = xlsxdb[26]
    df.loc[13][16] = xlsxdb[27]
    df.loc[14][16] = xlsxdb[28]
# sql = "INSERT INTO blood VALUES(id,'{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format \
#     (all_index['name'],all_index['sex'],all_index['age'],today,all_index['xt'], \
#     all_index['form'],all_index['doctor'],all_index['description'],all_index['wbc'], \
#     all_index['l'],all_index['m'],all_index['g'],all_index['lpre'],all_index['mpre'],all_index['gpre'], \
#     all_index['hgb'],all_index['rbc'],all_index['hct'],all_index['mcv'],all_index['mch'],all_index['mchc'], \
#     all_index['rdw_cv'],all_index['rdw_sd'],all_index['plt'],all_index['mpv'],all_index['pdw'],all_index['pct'], \
#     all_index['ppre'] \
#     )
push()
title = "血常规{}{}".format(today,xlsxdb[1])
pd.DataFrame(df).to_excel(excel_writer=title)
# writer.save()
cursor.close()
conn.close()
